package com.scalar.chiptrack.adaptor.ikanos.kyec;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.TestLotBinReport;
import com.scalar.chiptrack.adaptor.SortLotBinData;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.adaptor.dao.FabSortAdaptorDAO;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Date;
import java.io.File;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 17 Sep, 2009
 * Time: 10:20:21 AM
 * To change this template use File | Settings | File Templates.
 */
public class KYECDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, FabSortLot> m_sortDataMap       = new HashMap<String, FabSortLot>();
    private HashMap<String, ShippedLot> m_sortShipDataMap   = new HashMap<String, ShippedLot>();
    private HashMap<String, InventoryLot> m_fgDataMap       = new HashMap<String, InventoryLot>();
    private HashMap<String, InventoryLot> m_waferBankDataMap= new HashMap<String, InventoryLot>();
    private HashMap<String, ShippedLot> m_ftshipDataMap     = new HashMap<String, ShippedLot>();
    private HashMap<String, TestLot> m_testDataMap          = new HashMap<String, TestLot>();
    private HashMap<String, InventoryLot> m_preTestDataMap	= new HashMap<String, InventoryLot>();
    private HashMap<String, FabSortLot> m_sortBinDataMap    = new HashMap<String, FabSortLot>();
    private HashMap<String, FabSortLot> m_sortTotalMap      = new HashMap<String, FabSortLot>();

    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    public  KYECDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir( VENDOR_NAME_KYEC , WIP_TYPE_TEST );

            if ( m_dataOutputDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );

                m_parserLogger= AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_KYEC );

                AdaptorUtils.setLogger( m_parserLogger );
                ShippedLotDAO.setLogger( m_parserLogger);
                InventoryLotDAO.setLogger(m_parserLogger);
                TestAdaptorDAO.setLogger( m_parserLogger);
                FabSortAdaptorDAO.setLogger( m_parserLogger);
            }
        }
        catch ( Exception se )
        {
            log( "ERROR!! ERROR!! ERROR!! Exception while loading KYEC output directory details from database, Exception: " + se );
            System.out.println( "ERROR!! ERROR!! ERROR!! Exception while loading KYEC output directory details from database, Exception: " + se );
        }
    }
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new KYECDataParser().execute();
        }
        catch ( Exception e )
        {
            throw e;
        }
    }
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader( VENDOR_NAME_KYEC, CONSTANT_PARSER );

        File doneDestDir = new File( m_dataOutputDir + "/done" );
        File dataoutDir  = new File( m_dataOutputDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || (fileList.length <= 3))
        {
            log("No file found in directory: " + dataoutDir);
            System.out.println("No file found in directory: " + dataoutDir );
            throw new ScalarException("No file found in directory: " + dataoutDir+ "----\n\n" );
        }

        ArrayList<File> successFileList = new ArrayList<File>();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {
                 if ( fileList[i].getName().startsWith( KYECPOP3Adaptor.FILE_NAME_FT_WIP )  || fileList[i].getName().indexOf( KYECPOP3Adaptor.FILE_NAME_FT_WIP )  != -1)
                {
                     parseTestWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                 if ( fileList[i].getName().startsWith( KYECPOP3Adaptor.FILE_NAME_CP_WIP )  || fileList[i].getName().indexOf( KYECPOP3Adaptor.FILE_NAME_CP_WIP )  != -1)
                {
                     parseCPWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( fileList[i].getName().startsWith( KYECPOP3Adaptor.FILE_NAME_FT_SHIP ) )
                {
                    parseTestShipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }
                if ( fileList[i].getName().startsWith( KYECPOP3Adaptor.FILE_NAME_CP_SHIP ) )
                {
                    parseCPShipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }

                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(KYECPOP3Adaptor.FILE_NAME_FT_YIELD ) )
                {
                    ArrayList<TestLotBinReport> testBinList = parseFTYieldReport( fileList[i].getAbsolutePath() );

                   if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCS Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating KYEC FT Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_KYEC );
                            success = true;
                        }
                        else
                        {
                            log( "KYEC FT Binning Information List is Empty" );
                        }
                    }
                   successFileList.add( fileList[i] );
               }
               if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(KYECPOP3Adaptor.FILE_NAME_CP_YIELD ) )
               {
                   //For Saving the Sort Binning
                    ArrayList<SortLotBinData> sortBinList = parseCPBinReport( fileList[i].getAbsolutePath() );

                    if ( sortBinList != null && sortBinList.size() > 0 )
                    {
                        log("updateing the KYEC Cp Binning Info....");
                        FabSortAdaptorDAO.saveSortBinInfo( null, sortBinList, VENDOR_NAME_KYEC );
                        success = true;
                    }
                    successFileList.add( fileList[i] );
               }
                success = true;
            }
            catch (Exception e)
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
        }

        DBTransaction dbTransaction = null;

        try
        {
            // Save Wafer Store Information
            if (m_waferBankDataMap.isEmpty() == false)
            {
                log(" Updating Wafer Store Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_waferBankDataMap.values().iterator(), LOCATION_TYPE_WAFER_BANK, VENDOR_NAME_KYEC);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_WAFER_BANK, VENDOR_NAME_KYEC);
            }
            success = true;
            m_waferBankDataMap.clear();
            m_waferBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Wafer Store Lot information into database: " + se);
            throw se;
        }
        try
        {
            if ( m_sortBinDataMap.isEmpty() == false )
            {
                FabSortAdaptorDAO.saveSortBinData( null, m_sortBinDataMap.values().iterator() );
            }
            success = true;
            m_sortBinDataMap.clear();
        }
        catch(Exception e)
        {
            success = false;
            System.out.println(" Exception Occrred While Saving the Sort Binning Info " +e );
            throw e;
        }
        // to save the Sort lot tables
        try
        {
            if ( m_sortDataMap.isEmpty() == false )
            {
                log( "Updating Sort Lot Information" );
                FabSortAdaptorDAO.saveSortLot( null, m_sortDataMap.values().iterator(),WIP_TYPE_SORT, VENDOR_NAME_KYEC );
                success = true;
            }
            else
            {
                FabSortAdaptorDAO.closeFabSortLot( null,WIP_TYPE_SORT, VENDOR_NAME_KYEC );
            }

        }
        catch ( Exception e )
        {
            success = false;
            log( "ERROR!! ERROR!! ERROR!! Could not update KYEC Sort Data information into database: " + e );
            e.printStackTrace();
            throw e;
        }

        try
        {
            // Save Sort Information
            if ( m_sortShipDataMap.isEmpty() == false )
            {
                log( " Updating Sort Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_sortShipDataMap.values().iterator() );
            }
            success = true;
            m_sortShipDataMap.clear();
            m_sortShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Sort Shipping Lot information into database: " + se );
            throw se;
        }

        try
        {
           if ( m_preTestDataMap.isEmpty() == false )
           {
               log(" Updating Class Stroe Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_preTestDataMap.values().iterator(), LOCATION_TYPE_PRE_TEST, VENDOR_NAME_KYEC );
               success = true;
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_PRE_TEST, VENDOR_NAME_KYEC  );
           }
      }
      catch (ScalarException se)
      {
          success = false;
          se.printStackTrace();
          log("ERROR!! ERROR!! ERROR!! Could not update Pre Test Lot information into database: " + se);
          throw se;
      }
		 try
        {
           // Saving FINAL TEST Information
           if ( m_testDataMap.isEmpty() == false )
            {
                log( " Updating Test Lot Information" );
                TestAdaptorDAO.saveTestLot( dbTransaction,m_testDataMap.values().iterator(), LOCATION_TYPE_FT, VENDOR_NAME_KYEC );
            }
            else
           {
               TestAdaptorDAO.closeTestLot( dbTransaction, LOCATION_TYPE_FT, VENDOR_NAME_KYEC );
           }

           success = true;
        }
        catch ( ScalarException se )
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Test Lot information into database: " + se );
            throw se;
        }

        try
        {
            if (m_ftshipDataMap.isEmpty() == false)
            {
                log("Updating FG Shipment Information");
                ShippedLotDAO.saveShippedLot( dbTransaction, m_ftshipDataMap.values().iterator());
            }
            success = true;
        }
        catch (Exception e)
        {
            success = false;
            log("ERROR!! ERROR!! ERROR!! Could not update KYEC FT Shipping information into database: " + e);
            e.printStackTrace();
            throw e;
        }
       // to save the Finished Goods lot tables
       try
       {
           if ( m_fgDataMap.isEmpty() == false )
           {
               log(" Updating Finished Goods Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_fgDataMap.values().iterator(), LOCATION_TYPE_FG, VENDOR_NAME_KYEC );
               success = true;
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_FG, VENDOR_NAME_KYEC );
           }
      }
      catch (ScalarException se)
      {
          success = false;
          se.printStackTrace();
          log("ERROR!! ERROR!! ERROR!! Could not update Finished Goods Lot information into database: " + se);
          throw se;
      }

        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications( VENDOR_NAME_KYEC,true, true, true );

        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots( VENDOR_NAME_KYEC );

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( KYECPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);
        // transaction 9
        try
        {
            if (success)
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, KYECPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println("SUCCESSFULLY UPDATED IN THE DATABASE");
            }
        }
        catch (ScalarException se)
        {
            success = false;  //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, KYECPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log("WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se);
            throw se;
        }

        // Step 10
        // Move the successful files to 'done' directory
        if (success)
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir);
        }
    }

    private void parseTestWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        InventoryLot inventoryLot  = null;
        TestLot      testLot       = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String vendorLotNumber  = null;
        int dieQtyIn            = 0;
        String dateIn           = null;


        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(4);
            ((XLSParserImpl)parser).setDataStartRowIdx(5);

            log( "\nFound " + fileName );
            log( "\nParsing the file for LFT Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for LFT Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("MFG PartNo");
                fabLotNumber    = result.getString("Lot_No");
                vendorLotNumber = result.getString("Lot_InNo");
                if ( vendorLotNumber == null )
                {
                    vendorLotNumber = fabLotNumber;
                }
                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {                       
                    try
                    {
                        dieQtyIn = result.getInt("Current_Qty") ;
                    }
                    catch(NumberFormatException nfe)
                    {
                        dieQtyIn = 0;
                    }

                    if ( dieQtyIn > 0 )
                    {
                        if ( result.getDate( "IN_Date") != null )
                        {
                            dateIn = StringUtils.formatDateToString( result.getDate( "IN_Date"),  "MM/dd/yyyy HH:mm:ss" );
                        }
                        else if ( result.getDate( "RCV_Date") != null )
                        {
                            dateIn = StringUtils.formatDateToString( result.getDate( "RCV_Date"), "MM/dd/yyyy HH:mm:ss" );
                        }
                        
                        if ( result.getString("Status") != null && result.getString("Step") != null
                                && ( "FGI".equalsIgnoreCase( result.getString("Status").trim() )  || ( "FINISH".equalsIgnoreCase( result.getString("Status").trim() ) && "INV".equalsIgnoreCase(result.getString("Step").trim() ) ) ) )
                        {
                            log("IN FG lotNumber = " + vendorLotNumber);
                            inventoryLot = m_fgDataMap.get( vendorLotNumber + fabLotNumber + partNumber );

                            if( inventoryLot == null )
                            {
                                inventoryLot = new InventoryLot( VENDOR_NAME_KYEC,  LOCATION_TYPE_FG);
                                m_fgDataMap.put( vendorLotNumber + fabLotNumber + partNumber , inventoryLot );
                            }

                            inventoryLot.setPartNumber( partNumber );
                            inventoryLot.setLotNumber( vendorLotNumber );
                            inventoryLot.setFabLotNumber( fabLotNumber );
                            inventoryLot.setQtyIn( dieQtyIn+"" );
                            inventoryLot.setDateIn( dateIn );
                            inventoryLot.setStatus(LOT_STATUS_RUNNING);
                            if ("ENG".equalsIgnoreCase(result.getString("LotType")) )
                            {
                                inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_ENGG );
                            }
                            else if ("RMA".equalsIgnoreCase( result.getString("LotType") ) )
                            {
                                  inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_RMA );
                            }
                            else
                            {
                                inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                            }
                            inventoryLot.setDateCode( result.getString("DateCode") );
                            inventoryLot.setPkgType( result.getString("Package Type") );
                            inventoryLot.setCustomerPO(result.getString("PO_No"));
                        }
                        else if (result.getString("Status") != null && result.getString("Step") != null && ( "RECEIVE".equalsIgnoreCase( result.getString("Status").trim() ) || "IQC".equalsIgnoreCase(result.getString("Step").trim() ) ) )
                        {
                            log("In Pre Test" + vendorLotNumber);

                            inventoryLot = m_preTestDataMap.get( vendorLotNumber + fabLotNumber + partNumber );

                            if( inventoryLot == null )
                            {
                                inventoryLot = new InventoryLot( VENDOR_NAME_KYEC,  LOCATION_TYPE_PRE_TEST);
                                m_preTestDataMap.put( vendorLotNumber + fabLotNumber + partNumber , inventoryLot );
                            }
                            int quantity = 0;
                            if (inventoryLot.getQtyIn() != null)
                            {
                                try
                                {
                                    quantity = Integer.parseInt(inventoryLot.getQtyIn());
                                }
                                catch (NumberFormatException ignore) {
                                }
                                try
                                {
                                    quantity += dieQtyIn;
                                }
                                catch (NumberFormatException nfe) {
                                }
                            }
                            else
                            {
                                try
                                {
                                    quantity = dieQtyIn;
                                }
                                catch (NumberFormatException ignore) {
                                }
                            }
                            if (quantity > 0 )
                            {
                                inventoryLot.setPartNumber( partNumber );
                                inventoryLot.setLotNumber( vendorLotNumber );
                                inventoryLot.setFabLotNumber( fabLotNumber );
                                inventoryLot.setQtyIn( quantity+"" );
                                inventoryLot.setDateIn( dateIn );
                                inventoryLot.setStatus(LOT_STATUS_RUNNING);
                                if ("ENG".equalsIgnoreCase(result.getString("LotType")) )
                                {
                                    inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_ENGG );
                                }
                                else if ("RMA".equalsIgnoreCase( result.getString("LotType") ) )
                                {
                                      inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_RMA );
                                }
                                else
                                {
                                    inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                                }
                                inventoryLot.setDateCode( result.getString("DateCode") );
                                inventoryLot.setPkgType( result.getString("Package Type") );
                                inventoryLot.setCustomerPO(result.getString("PO_No"));
                            }
                        }
                        else
                        {
                            log("In FT" + vendorLotNumber );
                            testLot = m_testDataMap.get( vendorLotNumber + fabLotNumber + partNumber);

                            if (testLot == null)
                            {
                                testLot = new TestLot( VENDOR_NAME_KYEC);
                                m_testDataMap.put( vendorLotNumber + fabLotNumber + partNumber, testLot);
                            }

                            testLot.setQtyIn( dieQtyIn+"" );
                            testLot.setLotNumber( vendorLotNumber );
                            testLot.setPartNumber( partNumber );
                            if ( "HOLD".equalsIgnoreCase(result.getString("Status") ) )
                            {
                                testLot.setStatus( LOT_STATUS_HOLD );
                            }
                            else
                            {
                                testLot.setStatus( LOT_STATUS_RUNNING );
                            }
                            testLot.setFabLotNumber( fabLotNumber );
                            testLot.setPackageType( result.getString("Package Type") );
                            testLot.setDateCode( result.getString("DateCode") );
                            testLot.setPoNumber(result.getString("PO_No"));
                            testLot.setPsType( LOCATION_TYPE_FT );
                            testLot.setStage( result.getString("Step") );
                            if ( "ENG".equalsIgnoreCase(result.getString("LotType")))
                            {
                                testLot.setCategory ( LOT_CATEGORY_TYPE_ENGG );
                            }
                            else if ("RMA".equalsIgnoreCase( result.getString("LotType") ))
                            {
                                 testLot.setCategory( LOT_CATEGORY_TYPE_RMA );
                            }
                            else
                            {
                                testLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                            }
                            testLot.setDateIn( dateIn );

                        }
                    }
                }

            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private void parseTestShipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;
        String partNumber                = null;
        String lotNumber                 = null;
        ShippedLot testShippedLot        = null;
        String fabLotNumber              = null;
        String nextLocation              = null;
        String shipDate                  = null;
        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            log( "\nFound " + fileName );
            log( "\nParsing the file for FT Ship Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FT Ship Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("Mfg Part # (Ikanos new part #)");
                fabLotNumber    = result.getString("Ikanos Lot #");
                lotNumber = result.getString("Subcon Lot #");
                if ( lotNumber == null )
                {
                    lotNumber = fabLotNumber;
                }
                if ( result.getString("SHIPDATE") != null )
                {
                    shipDate = StringUtils.formatDate(result.getString("SHIPDATE"), "yyyyMMdd", "MM/dd/yyyy" );
                }
                
                nextLocation = result.getString("Ship To");
                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                      testShippedLot = ( ShippedLot ) m_ftshipDataMap.get ( partNumber + lotNumber + fabLotNumber + nextLocation + shipDate );

                       if ( testShippedLot == null )
                       {
                            testShippedLot = new ShippedLot( VENDOR_NAME_KYEC, LOCATION_TYPE_FT );
                            m_ftshipDataMap.put( partNumber + lotNumber + fabLotNumber + nextLocation + shipDate, testShippedLot );
                       }

                       int quantity = 0;
                       if ( testShippedLot.getShipQty() != null )
                       {
                           try
                           {
                               quantity = Integer.parseInt( testShippedLot.getShipQty() );
                           }
                           catch ( NumberFormatException ignore )
                           {
                               quantity = 0;
                           }
                           try
                           {
                               quantity += result.getInt("SHIP_DIE_QTY");
                           }
                           catch ( NumberFormatException ignore )
                           {
                               quantity = 0;
                           }
                       }
                       else
                       {
                           try
                           {
                               quantity = result.getInt("SHIP_DIE_QTY");
                           }
                           catch ( NumberFormatException ignore )
                           {
                               quantity = 0;
                           }
                       }

                       if ( quantity > 0 )
                       {
                           testShippedLot.setShippingDoc( result.getString("Ship Request #") );
                           testShippedLot.setLotNumber( lotNumber );
                           testShippedLot.setPartNumber( partNumber );
                           testShippedLot.setShipDate( shipDate );
                           testShippedLot.setShipQty( quantity + "" );
                           testShippedLot.setCustomerPO( result.getString("End Customer PO #"));
                           testShippedLot.setNextLocation( nextLocation );
                           testShippedLot.setFabLotNumber( fabLotNumber );
                           testShippedLot.setProcessType( LOCATION_TYPE_FT );
                       }
                }

            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private void parseCPWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        InventoryLot inventoryLot  = null;
        FabSortLot   sortLot      = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String dateIn           = null;


        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(4);
            ((XLSParserImpl)parser).setDataStartRowIdx(5);

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("Cust. Device No");
                fabLotNumber    = result.getString("Cust. Lot No.");
               
                int waferQtyIn            = 0;
                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    try
                    {
                        waferQtyIn = result.getInt("Current Qty.") ;
                    }
                    catch(NumberFormatException nfe)
                    {
                        waferQtyIn = 0;
                    }

                    if ( waferQtyIn > 0 )
                    {
                        if ( result.getDate( "Receive Date") != null )
                        {
                            dateIn = StringUtils.formatDateToString( result.getDate( "Receive Date"), "MM/dd/yyyy HH:mm:ss" );
                        }

                        if ( result.getString("Status") != null  && result.getString("Stage") != null
                                && ( "RECEIVE".equalsIgnoreCase( result.getString("Status").trim() ) || "TEMP".equalsIgnoreCase( result.getString("Status").trim() ) || "IQC".equalsIgnoreCase( result.getString("Stage").trim() ) ) )
                        {
                            log("IN WaferStore lotNumber = " + fabLotNumber);
                            inventoryLot = m_waferBankDataMap.get( fabLotNumber + partNumber );

                            if( inventoryLot == null )
                            {
                                inventoryLot = new InventoryLot( VENDOR_NAME_KYEC,  LOCATION_TYPE_WAFER_STORE);
                                m_waferBankDataMap.put(  fabLotNumber + partNumber , inventoryLot );
                            }
                            int quantity = 0;
                            if (inventoryLot.getWaferQtyIn() != null)
                            {
                                try
                                {
                                    quantity = Integer.parseInt( inventoryLot.getWaferQtyIn() );
                                }
                                catch (NumberFormatException npe) {
                                }
                                try
                                {
                                    quantity += waferQtyIn;
                                }
                                catch (NumberFormatException npe) {
                                }
                            }
                            else
                            {
                                try
                                {
                                    quantity = waferQtyIn;
                                }
                                catch (NumberFormatException npe) {
                                }
                            }
                            if ( quantity > 0 )
                            {
                                inventoryLot.setPartNumber( partNumber );
                                inventoryLot.setLotNumber( fabLotNumber );
                                inventoryLot.setFabLotNumber( fabLotNumber );
                                inventoryLot.setWaferQtyIn( quantity+"" );
                                inventoryLot.setDateIn( dateIn );
                                inventoryLot.setStatus(LOT_STATUS_RUNNING);
                                inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                                inventoryLot.setStage( result.getString("Stage") );
                                if (result.getDate("Commit Date") != null)
                                {
                                    inventoryLot.setExpectedDate( StringUtils.formatDateToString( result.getDate("Commit Date"), "MM/dd/yyyy HH:mm:ss" ));
                                }
                            }

                        }
                        else
                        {
                            log("In sort" + fabLotNumber );
                            sortLot = m_sortDataMap.get( fabLotNumber + partNumber);

                            if (sortLot == null)
                            {
                                sortLot = new FabSortLot( VENDOR_NAME_KYEC, WIP_TYPE_SORT );
                                m_sortDataMap.put( fabLotNumber + partNumber, sortLot);
                            }
                            int quantity = 0;
                            String waferIds = null;
                            if (sortLot.getQtyIn() != null)
                            {
                                try
                                {
                                    quantity = Integer.parseInt( sortLot.getQtyIn() );
                                }
                                catch (NumberFormatException npe) {
                                }
                                try
                                {
                                    quantity += waferQtyIn;
                                }
                                catch (NumberFormatException npe) {
                                }

                            }
                            else
                            {
                                try
                                {
                                    quantity = waferQtyIn;
                                }
                                catch (NumberFormatException npe) {
                                }
                            }
                            if ( sortLot.getWaferID() != null )
                            {
                                waferIds = sortLot.getWaferID() + AdaptorUtils.getWaferIDsByFormat( result.getString("Wafer ID"));
                            }
                            else
                            {
                                waferIds = AdaptorUtils.getWaferIDsByFormat( result.getString("Wafer ID"));
                            }

                            if ( quantity > 0  )
                            {
                                sortLot.setQtyIn( quantity+"" );
                                sortLot.setLotId( fabLotNumber );
                                sortLot.setCustProduct( partNumber );
                                if ( "HOLD".equalsIgnoreCase(result.getString("Status") ) )
                                {
                                    sortLot.setLotStatus( LOT_STATUS_HOLD );
                                    sortLot.setHoldReason( result.getString("Hold Reason"));
                                }
                                else
                                {
                                    sortLot.setLotStatus( LOT_STATUS_RUNNING );
                                }
                                sortLot.setWaferID( waferIds );
                                sortLot.setFabLotNumber( fabLotNumber );
                                sortLot.setPsType( LOCATION_TYPE_SORT );
                                sortLot.setStage( result.getString("Stage") );
                                sortLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                                sortLot.setDateIn( dateIn );
                                if (result.getDate("Commit Date") != null)
                                {
                                    sortLot.setForcastedDateOut( StringUtils.formatDateToString( result.getDate("Commit Date"), "MM/dd/yyyy HH:mm:ss" ));
                                }    

                            }
                        }
                    }
                }

            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private void parseCPShipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;
        ShippedLot shippedLot   = null;        
        String partNumber       = null;
        String fabLotNumber     = null;
        String shipDate         = null;
        String nextLocation     = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(4);
            ((XLSParserImpl)parser).setDataStartRowIdx(5);

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Ship Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Ship Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("Ikanos Fab Part #");
                fabLotNumber    = result.getString("Ikanos Lot #");

                if (result.getDate("ShipDate") != null )
                {
                    shipDate =  StringUtils.formatDateToString(result.getDate("ShipDate"), "MM/dd/yyyy HH:mm:ss" );
                }
                nextLocation = result.getString("ShipTo");
                
                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    shippedLot = ( ShippedLot ) m_sortShipDataMap.get ( partNumber + fabLotNumber + nextLocation + shipDate );

                   if ( shippedLot == null )
                   {
                        shippedLot = new ShippedLot(VENDOR_NAME_KYEC, LOCATION_TYPE_SORT);
                        m_sortShipDataMap.put( partNumber + fabLotNumber + nextLocation + shipDate, shippedLot );
                   }

                   int quantity = 0;
                   int dquantity = 0;
                   if ( shippedLot.getShipQty() != null )
                   {
                       try
                       {
                           dquantity = Integer.parseInt( shippedLot.getShipDieQty() );
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                           dquantity =0;
                       }

                       quantity += result.getInt("OutputQty") ;
                       dquantity += result.getInt("TotalGoodDie");
                   }
                   else
                   {
                       quantity =  result.getInt("OutputQty") ;
                       dquantity = result.getInt("TotalGoodDie");
                   }
                    //System.out.println("result.getString(\"Wafer ID\") = " + result.getString("Wafer ID"));
                    //System.out.println("ENCODED STRING = " + StringUtils.encode( result.getString("Wafer ID") ) );
                    //System.out.println("result.getString(\"Wafer ID\") = " + AdaptorUtils.getWaferIDsByFormat(result.getString("Wafer ID") ));
                   String waferIds = null;
                   if ( shippedLot.getWaferID() != null )
                   {
                         waferIds =  shippedLot.getWaferID();

                        waferIds = waferIds + ";" + AdaptorUtils.getWaferIDsByFormat(result.getString("Wafer ID") );
                   }
                   else
                   {
                       waferIds = AdaptorUtils.getWaferIDsByFormat(result.getString("Wafer ID") );
                   }

                   if ( quantity > 0 )
                   {
                       shippedLot.setLotNumber( fabLotNumber );
                       shippedLot.setPartNumber( partNumber );
                       shippedLot.setShipDate( shipDate );
                       shippedLot.setShipQty( quantity + "" );
                       shippedLot.setNextLocation( nextLocation );
                       shippedLot.setWaferID( waferIds );
                       shippedLot.setFabLotNumber( fabLotNumber );
                       shippedLot.setProcessType( LOCATION_TYPE_SORT );
                       shippedLot.setShipDieQty( dquantity + "");
                       shippedLot.setShippingDoc( result.getString("ShippingNo"));
                   }
                }
           }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }
    private ArrayList<TestLotBinReport> parseFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FT Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("DEVICE");
                fablotNumber = result.getString("Wafer Lot No");
                lotNumber    = result.getString("Subcon Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("L/C") );
                    ftYieldStatus.setStage( result.getString("INSERTION") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Program No"));
                    ftYieldStatus.setTesterId( result.getString("Tester ID") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler ID") );
                    ftYieldStatus.setLb(result.getString("L/B ID"));
                    ftYieldStatus.setTemp( result.getString("Temp"));
                    ftYieldStatus.setDateIn( StringUtils.formatDate( result.getString("Test In Date"), "yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setDateOut( StringUtils.formatDate( result.getString("Test Out Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Out Qty")+"" );
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );
                    ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getString("Bin 1") );
                    ftYieldStatus.setBin2(result.getString("Bin 2"));
                    ftYieldStatus.setBin3(result.getString("Bin 3"));
                    ftYieldStatus.setBin4(result.getString("Bin 4"));
                    ftYieldStatus.setBin5(result.getString("Bin 5"));
                    ftYieldStatus.setBin6(result.getString("Bin 6"));
                    ftYieldStatus.setBin7(result.getString("Bin 7"));
                    ftYieldStatus.setBin8(result.getString("Bin 8"));

                    ftYieldStatus.setLossQty( result.getString("Loss QTY") );
                    ftYieldStatus.setVmQty( result.getString("VM QTY") );
                    ftYieldStatus.setOsQty( result.getString("OS QTY") );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the KYEC FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }

    private ArrayList<SortLotBinData> parseCPBinReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        FabSortLot sortBinTotalLot = null;
        FabSortLot sortBinLot   = null;
        ArrayList<SortLotBinData> cpYieldList  = new ArrayList<SortLotBinData>();
         int waferId             = 0;
        HashMap waferIdMap = new HashMap();
        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for CP Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for CP Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );
             while ( result.next() )
            {
                partNumber = result.getString("Device");
                lotNumber = result.getString("Ikanons Lot No");
                waferId   = result.getInt("Wafer #");

                if (partNumber!= null && partNumber.trim().length()>0 && lotNumber != null && lotNumber.trim().length() > 0 && waferId > 0 && waferId < 26 )
                {
                    sortBinTotalLot = (FabSortLot) m_sortTotalMap.get( partNumber + lotNumber);

                    if ( sortBinTotalLot == null )
                    {
                        sortBinTotalLot = new FabSortLot( VENDOR_NAME_KYEC, LOCATION_TYPE_SORT );
                        m_sortTotalMap.put( partNumber + lotNumber  , sortBinTotalLot );
                    }

                    int m_totalProbeQty         = 0;
                    int m_totalPassQty          = 0;
                    int m_waferQtyIn            = 0;
                    if ( sortBinTotalLot.getQtyIn() != null )
                    {
                       try
                       {
                           m_waferQtyIn = Integer.parseInt( sortBinTotalLot.getQtyIn() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                       }

                       m_totalPassQty = sortBinTotalLot.getTotalgoodDieQtyIn();
                       m_totalProbeQty = sortBinTotalLot.getTotalprobeQtyIn();

                       if ( !waferIdMap.containsKey( partNumber + lotNumber + waferId ) )
                       {
                           waferIdMap.put( partNumber + lotNumber+waferId, waferId );
                            m_waferQtyIn += 1;

                            m_totalPassQty += result.getInt("good Dies");
                            m_totalProbeQty += result.getInt("GDPW");
                       }
                    }
                    else
                    {
                        if ( !waferIdMap.containsKey( partNumber + lotNumber+waferId ) )
                        {
                            waferIdMap.put( partNumber + lotNumber+waferId, waferId );
                            m_waferQtyIn = 1;
                            m_totalPassQty = result.getInt("good Dies");
                            m_totalProbeQty = result.getInt("GDPW");
                        }
                    }

                    /*if ( sortBinTotalLot.getDateOut() != null && result.getString("Test Out Date") != null )
                    {
                         Date tempDate1 = StringUtils.format(sortBinTotalLot.getDateOut());
                         Date tempDate2 = StringUtils.format(result.getString("Test Out Date") );

                        if ( tempDate1.compareTo( tempDate2 ) < 0 )
                        {
                            sortBinTotalLot.setDateOut( result.getString("Test Out Date") );
                        }
                    }
                    else
                    {
                        sortBinTotalLot.setDateOut( result.getString("Test Out Date") );
                    }*/
                    sortBinTotalLot.setQtyIn( m_waferQtyIn+"");
                    sortBinTotalLot.setTotalgoodDieQtyIn( m_totalPassQty);
                    sortBinTotalLot.setTotalprobeQtyIn( m_totalProbeQty );
                }
            }
            //FOR Restting the Row Index
            result.reset();

            while ( result.next() )
            {
                partNumber   = result.getString("Device");
                fablotNumber = result.getString("Ikanons Lot No");
                lotNumber    = result.getString("Ikanons Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }
                waferId   = result.getInt("Wafer #");

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 && waferId > 0 && waferId < 26 )
                {
                    SortLotBinData cpYieldStatus = new SortLotBinData();

                    sortBinLot = ( FabSortLot ) m_sortBinDataMap.get( partNumber + lotNumber + waferId);

                    if ( sortBinLot == null )
                    {
                        sortBinLot = new FabSortLot( VENDOR_NAME_KYEC, LOCATION_TYPE_SORT );
                        m_sortBinDataMap.put( partNumber + lotNumber + waferId  , sortBinLot );
                    }
                    sortBinLot.setCustProduct( partNumber );
                    sortBinLot.setLotId( lotNumber );
                    sortBinLot.setFabLotNumber( lotNumber );
                    sortBinLot.setWaferID( waferId+"" );
                     // WAFER QTY IN Always Same(total wafers) ,But For Each every Wafer update the Wafer Qty, the the WaferQty

                    sortBinLot.setQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getQtyIn() +"");
                    sortBinLot.setTotalgoodDieQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getTotalgoodDieQtyIn() );
                    sortBinLot.setTotalprobeQtyIn( m_sortTotalMap.get(partNumber + lotNumber).getTotalprobeQtyIn() );

                    sortBinLot.setDateOut( StringUtils.formatDate( result.getString("Test Out Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    sortBinLot.setDateIn( StringUtils.formatDate( result.getString("Test In Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    sortBinLot.setTesterNo( result.getString("Tester ID") );
                    sortBinLot.setTestProgram( result.getString("Program No") );
                    sortBinLot.setGoodDieQtyIn(result.getInt("good Dies"));
                    sortBinLot.setRejectedQtyIn(result.getInt("GDPW")-result.getInt("good Dies"));
                    sortBinLot.setShipYield(result.getString("Yield").replaceAll("%","") );
                    sortBinLot.setTotalYield( 0 ); // if sets as 0, in the PROC Yield Calculating (INQTY *100.00/OUTQTY)
                    sortBinLot.setBinNumber( "Bin1" );

                    cpYieldStatus.setProduct( partNumber );
                    cpYieldStatus.setLotNumber(lotNumber );
                    cpYieldStatus.setFablotNumber( fablotNumber );
                    cpYieldStatus.setTemp(result.getString("Temperature"));
                    cpYieldStatus.setProbeId(result.getString("Prober ID"));
                    cpYieldStatus.setProbeCard(result.getString("Probe Card"));
                    cpYieldStatus.setInsertion(result.getString("Insertion"));
                    cpYieldStatus.setGrossDice( result.getInt("GDPW")+"");
                    cpYieldStatus.setGoodDice( result.getInt("good Dies")+"");
                    cpYieldStatus.setShipYield( result.getString("Yield").replaceAll("%","") );
                    cpYieldStatus.setWaferId( waferId+"");
                    cpYieldStatus.setDateIn( StringUtils.formatDate( result.getString("Test In Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    int[] binQty = new int[100];
                    int binNo = 0;
                    for(int i=1; i<=100; i++ )
                    {
                       /*try
                        {*/
                            binQty[binNo++] = result.getInt("Bin "+i);
                        /*}
                        catch(NumberFormatException nfe)
                        {
                            binQty[binNo++] = 0;
                        }*/

                    }
                    cpYieldStatus.setBinQty( binQty );

                    cpYieldList.add( cpYieldStatus );
                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the KYEC CP Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return cpYieldList;
    }
    private void log( String message )
    {
      if ( message == null ) return;

      if ( m_parserLogger != null )
      {
          m_parserLogger.logDebug( message + LINE_SEPARATOR );
      }
      else
      {
          System.out.println( message );
      }
    } // end of log method

}
